package com.gmail.blackdog1987.onedollar.dao;

import com.gmail.blackdog1987.onedollar.dao.hibernate.EntityHibernateDao;
import com.gmail.blackdog1987.onedollar.dao.hibernate.jdbc.BaseJdbcDao;
import com.gmail.blackdog1987.onedollar.dao.hibernate.jdbc.JdbcUtil;
import com.gmail.blackdog1987.onedollar.entity.TGoodsOnedollarEntity;
import com.gmail.blackdog1987.onedollar.entity.TUserEntity;
import com.gmail.blackdog1987.onedollar.entity.base.Page;
import com.gmail.blackdog1987.onedollar.vo.GoodsOneDollarVO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.List;

@Repository("oneDorllarGoodsDao")
public class OneDorllarGoodsDao extends EntityHibernateDao<TGoodsOnedollarEntity>{
    @Autowired
    private BaseJdbcDao baseJdbcDao;

    public Page<GoodsOneDollarVO> queryForPage(int pageindex, int pagesize, int sort, String category, int type){
        String whereSql;
        ArrayList<Object> whereArgs = new ArrayList<Object>();
        whereSql = " FROM t_goods_onedollar o\n" +
                " INNER JOIN t_goods t ON o.goods_id = t.goods_id \n"+
                " LEFT JOIN t_goods_picture pic ON o.goods_id = pic.goods_id AND pic.is_main=1\n";
        if(category!=null&&category.trim().length()!=0&&!"-1".equals(category)){
            whereSql = whereSql +  " INNER JOIN t_goods_category gc on gc.cat_id = t.cat_id AND gc.parent_id = " + category + " ";
        }
        whereSql = whereSql + " WHERE 1=1 ";
        if(type!=0){
            whereSql = whereSql + " AND o.type = " + type ;
        }
        if(sort == 0){
            whereSql = whereSql +
                    " AND o.fill >= o.need AND o.totime > NOW()\n"+
                    " ORDER BY o.totime DESC ";
        } else if(sort == 4){
            whereSql = whereSql + " AND o.fromtime<NOW() ";
            whereSql = whereSql +
                    " AND o.fill < o.need "+
                    " ORDER BY o.fromtime DESC ";
        } else if(sort == 1){
            whereSql = whereSql + " AND o.fromtime<NOW() ";
            whereSql = whereSql + " AND o.fill < o.need "+" ORDER BY o.fill DESC ";
        } else if(sort == 2){
            whereSql = whereSql + " AND o.fromtime<NOW() ";
            whereSql = whereSql + " AND o.fill < o.need "+" ORDER BY t.`default_price` DESC ";
        } else if(sort == 3){
            whereSql = whereSql + " AND o.fromtime<NOW() ";
            whereSql = whereSql + " AND o.fill < o.need "+" ORDER BY t.`default_price` ";
        } else if(sort == 5){
            whereSql = whereSql + " AND o.need <= o.fill ORDER BY o.totime DESC ";
        }


        String selectionSql = " SELECT o.`id`,\n" +
                "       o.`goods_id`,\n" +
                "       o.`fromtime`,\n" +
                "       o.`totime`,\n" +
                "       o.`need`,\n" +
                "       o.`fill`,\n" +
                "       o.`assume`,\n" +
                "       o.`lasttime`,\n" +
                "       o.`firsttime`,\n" +
                "       o.`stage`,\n" +
                "       o.`name`,\n" +
                "       o.`subname`,\n" +
                "       o.`endtime`,\n" +
                "       o.`type`,\n" +
                "       o.`percent`,\n" +
                "       t.`seller_id`,\n" +
                "       t.`brand_id`,\n" +
                "       t.`cat_id`,\n" +
                "       t.`goods_name`,\n" +
                "       t.`keywords`,\n" +
                "       t.`is_shelves`,\n" +
                "       t.`shelf_cause`,\n" +
                "       t.`goods_info`,\n" +
                "       t.`goods_param`,\n" +
                "       t.`is_recommend`,\n" +
                "       t.`sort_order`,\n" +
                "       t.`add_time`,\n" +
                "       t.`update_time`,\n" +
                "       t.`status`,\n" +
                "       pic.`pic_url`,\n" +
                "       t.`default_price` old_price,\n" +
                "       t.`memo` ";


        String limit = " limit " + (pageindex-1)*pagesize + "," + pagesize;
        Integer totalCount = baseJdbcDao.getJdbcTemplate().queryForInt(" SELECT COUNT(*) " + whereSql , null);

        List<GoodsOneDollarVO> list = JdbcUtil.queryForList(baseJdbcDao, GoodsOneDollarVO.class, selectionSql + whereSql + limit, null);
//        System.err.print(selectionSql + whereSql + limit);

        Page page = new Page(list, pageindex, totalCount, pagesize);

        return page;
    }

    public Page<GoodsOneDollarVO> queryForPageByGoodsId(int pageindex, int pagesize, int goodsid, int type){
        String whereSql;
        whereSql = " FROM t_goods_onedollar o\n" +
                " INNER JOIN t_goods t ON o.goods_id = t.goods_id \n"+
                " LEFT JOIN t_goods_picture pic ON o.goods_id = pic.goods_id AND pic.is_main=1\n"+
                " INNER JOIN t_goods_category gc on gc.cat_id = t.cat_id\n";
        whereSql = whereSql + " WHERE o.goods_id = ? AND o.type = ? ORDER BY o.stage DESC ";

        String selectionSql = " SELECT o.`id`,\n" +
                "       o.`goods_id`,\n" +
                "       o.`fromtime`,\n" +
                "       o.`totime`,\n" +
                "       o.`need`,\n" +
                "       o.`fill`,\n" +
                "       o.`assume`,\n" +
                "       o.`lasttime`,\n" +
                "       o.`firsttime`,\n" +
                "       o.`stage`,\n" +
                "       o.`name`,\n" +
                "       o.`subname`,\n" +
                "       o.`endtime`,\n" +
                "       o.`type`,\n" +
                "       o.`percent`,\n" +
                "       t.`seller_id`,\n" +
                "       t.`brand_id`,\n" +
                "       t.`cat_id`,\n" +
                "       t.`goods_name`,\n" +
                "       t.`keywords`,\n" +
                "       t.`is_shelves`,\n" +
                "       t.`shelf_cause`,\n" +
                "       t.`goods_info`,\n" +
                "       t.`goods_param`,\n" +
                "       t.`is_recommend`,\n" +
                "       t.`sort_order`,\n" +
                "       t.`add_time`,\n" +
                "       t.`update_time`,\n" +
                "       t.`status`,\n" +
                "       pic.`pic_url`,\n" +
                "       t.`default_price` old_price,\n" +
                "       t.`memo` ";


        String limit = " limit " + (pageindex-1)*pagesize + "," + pagesize;
        Integer totalCount = baseJdbcDao.getJdbcTemplate().queryForInt(" SELECT COUNT(*) " + whereSql , new Object[]{goodsid, type});

        List<GoodsOneDollarVO> list = JdbcUtil.queryForList(baseJdbcDao, GoodsOneDollarVO.class, selectionSql + whereSql + limit, new Object[]{goodsid, type});

        Page page = new Page(list, pageindex, totalCount, pagesize);

        return page;
    }

    public Page<GoodsOneDollarVO> queryMyOneForPage(int pageindex, int pagesize, int userid, int type, String status){
        String whereSql;
        ArrayList<Object> whereArgs = new ArrayList<Object>();
        whereSql = " FROM t_goods_onedollar o\n" +
                " LEFT JOIN t_goods t ON o.goods_id = t.goods_id\n" +
                " LEFT JOIN t_order_onedollar oo ON oo.onedollar_id = o.id AND oo.user_id = ?\n" +
                " LEFT JOIN t_goods_picture pic ON o.goods_id = pic.goods_id AND pic.is_main=1\n" +
                "                WHERE o.type=?\n" +
                "                AND o.id IN (SELECT oo.onedollar_id FROM t_order_onedollar oo WHERE oo.user_id = ?)\n";

        if("1".equals(status)){
            whereSql += " AND (o.endtime > now() OR o.endtime is null)";
        } else if("2".equals(status)){
            whereSql += " AND o.endtime < now() ";
        }

        whereSql += " GROUP BY oo.onedollar_id ";
        whereSql += " ORDER BY add_time DESC ";

        String selectionSql = " SELECT o.`id`,\n" +
                "       o.`goods_id`,\n" +
                "       o.`fromtime`,\n" +
                "       o.`totime`,\n" +
                "       o.`need`,\n" +
                "       o.`fill`,\n" +
                "       o.`type`,\n" +
                "       o.`assume`,\n" +
                "       o.`lasttime`,\n" +
                "       o.`stage`,\n" +
                "       o.`firsttime`,\n" +
                "       o.`name`,\n" +
                "       o.`subname`,\n" +
                "       o.`endtime`,\n" +
                "       o.`percent`,\n" +
                "       t.`seller_id`,\n" +
                "       t.`brand_id`,\n" +
                "       t.`cat_id`,\n" +
                "       t.`goods_name`,\n" +
                "       t.`keywords`,\n" +
                "       t.`is_shelves`,\n" +
                "       t.`shelf_cause`,\n" +
                "       t.`goods_info`,\n" +
                "       t.`goods_param`,\n" +
                "       t.`is_recommend`,\n" +
                "       t.`sort_order`,\n" +
                "       oo.`onedollar_id`,\n" +
                "       MAX(oo.createtime) add_time,\n" +
                "       t.`update_time`,\n" +
                "       t.`status`,\n" +
                "       pic.`pic_url`,\n" +
                "       t.`default_price` old_price,\n" +
                "       t.`memo` ";


        String limit = " limit " + (pageindex-1)*pagesize + "," + pagesize;
        Integer totalCount = baseJdbcDao.getJdbcTemplate().queryForInt(" SELECT COUNT(*) FROM ( " + selectionSql + whereSql + ") q" , new Object[]{userid, type, userid});

        List<GoodsOneDollarVO> list = JdbcUtil.queryForList(baseJdbcDao, GoodsOneDollarVO.class, selectionSql + whereSql + limit, new Object[]{userid, type, userid});

        Page page = new Page(list, pageindex, totalCount, pagesize);

        System.out.println("page_________  "+page.toString());

        return page;
    }


    public GoodsOneDollarVO query(int userid, int id){
        String whereSql;
        whereSql = " FROM t_goods_onedollar o\n" +
                " INNER JOIN t_goods t ON o.goods_id = t.goods_id\n"+
                " LEFT JOIN t_goods_picture pic ON o.goods_id = pic.goods_id AND pic.is_main=1\n"+
                " LEFT JOIN t_order_onedollar oo ON o.id = oo.onedollar_id AND oo.user_id = ?\n"+
                " INNER JOIN t_goods_category gc on gc.cat_id = t.cat_id\n"+
                " WHERE o.id = ?";

        String selectionSql = " SELECT o.`id`,\n" +
                "       o.`goods_id`,\n" +
                "       o.`fromtime`,\n" +
                "       o.`totime`,\n" +
                "       o.`need`,\n" +
                "       o.`stage`,\n" +
                "       o.`type`,\n" +
                "       o.`fill`,\n" +
                "       o.`assume`,\n" +
                "       o.`lasttime`,\n" +
                "       o.`firsttime`,\n" +
                "       o.`name`,\n" +
                "       o.`subname`,\n" +
                "       o.`endtime`,\n" +
                "       o.`percent`,\n" +
                "       t.`seller_id`,\n" +
                "       t.`brand_id`,\n" +
                "       t.`cat_id`,\n" +
                "       t.`goods_name`,\n" +
                "       t.`keywords`,\n" +
                "       t.`is_shelves`,\n" +
                "       t.`shelf_cause`,\n" +
                "       t.`goods_info`,\n" +
                "       t.`goods_param`,\n" +
                "       t.`is_recommend`,\n" +
                "       t.`sort_order`,\n" +
                "       t.`add_time`,\n" +
                "       t.`update_time`,\n" +
                "       t.`status`,\n" +
                "       pic.`pic_url`,\n" +
                "       oo.`ticket_code`,\n" +
                "       t.`default_price` old_price,\n" +
                "       t.`memo` ";

        List<GoodsOneDollarVO> list = JdbcUtil.queryForList(baseJdbcDao, GoodsOneDollarVO.class, selectionSql + whereSql, new String[]{String.valueOf(userid), String.valueOf(id)});

        return list==null||list.size()==0?null:list.get(0);
    }

    public Page<GoodsOneDollarVO> queryForWin(int userid, int pageindex, int pagesize, int type){
        String whereSql;
        whereSql = " FROM t_goods_onedollar o\n" +
                " INNER JOIN t_goods t ON o.goods_id = t.goods_id\n" +
                " LEFT JOIN t_goods_picture pic ON o.goods_id = pic.goods_id AND pic.is_main=1\n" +
                " INNER JOIN t_onedollar_ticket oo ON o.id = oo.onedollar_id AND oo.user_id = ?";

        if(type != 0){
            whereSql = whereSql + " AND o.type = " + type;
        }

        String selectionSql = " SELECT o.`id`,\n" +
                "       o.`goods_id`,\n" +
                "       o.`fromtime`,\n" +
                "       o.`totime`,\n" +
                "       o.`need`,\n" +
                "       o.`stage`,\n" +
                "       o.`type`,\n" +
                "       o.`fill`,\n" +
                "       o.`assume`,\n" +
                "       o.`lasttime`,\n" +
                "       o.`firsttime`,\n" +
                "       o.`name`,\n" +
                "       o.`subname`,\n" +
                "       o.`endtime`,\n" +
                "       o.`percent`,\n" +
                "       t.`seller_id`,\n" +
                "       t.`brand_id`,\n" +
                "       t.`cat_id`,\n" +
                "       t.`goods_name`,\n" +
                "       t.`keywords`,\n" +
                "       t.`is_shelves`,\n" +
                "       t.`shelf_cause`,\n" +
                "       t.`goods_info`,\n" +
                "       t.`goods_param`,\n" +
                "       t.`is_recommend`,\n" +
                "       t.`sort_order`,\n" +
                "       t.`add_time`,\n" +
                "       t.`update_time`,\n" +
                "       t.`status`,\n" +
                "       pic.`pic_url`,\n" +
                "       oo.code ticket_code,\n" +
                "       t.`default_price` old_price,\n" +
                "       t.`memo` ";

        String limit = " limit " + (pageindex-1)*pagesize + "," + pagesize;
        Integer totalCount = baseJdbcDao.getJdbcTemplate().queryForInt(" SELECT COUNT(*) FROM ( " + selectionSql + whereSql + ") q" , new Object[]{userid});

        List<GoodsOneDollarVO> list = JdbcUtil.queryForList(baseJdbcDao, GoodsOneDollarVO.class, selectionSql + whereSql + limit, new Object[]{userid});

        Page page = new Page(list, pageindex, totalCount, pagesize);

        return page;
    }
}
